package cn.com.chec.en.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.chec.en.constants.LogConstants;
import cn.com.chec.en.model.domain.LogInfo;
import cn.com.chec.en.model.domain.LogOpr;
import cn.com.chec.en.model.domain.UserInfo;
import cn.com.chec.en.model.dto.LogReportDTO;

@Repository
public class LogInfoDao {
	protected static final Logger log = Logger.getLogger(LogInfoDao.class);

	public boolean insert(LogInfo info) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String insert = "insert into tbl_log_i(log_userid_fk,log_action_id,log_channelid_fk,log_desc,log_time) values(?,?,?,?,?)";

			PreparedStatement pstmt = conn.prepareStatement(insert);

			int i = 0;
			i++;
			pstmt.setInt(i, info.getUserId());
			i++;
			pstmt.setInt(i, info.getLogActionId());
			i++;
			pstmt.setInt(i, info.getLogChannelId());
			i++;
			pstmt.setString(i, info.getLogDesc());
			i++;
			pstmt.setString(i, info.getLogTime());
			pstmt.execute();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public List<LogInfo> getSubList(LogInfo info, String startDate,
			String endDate, int start, int num) {
		List<LogInfo> list = new ArrayList<LogInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_log_i where 1=1 ";

			if (info.getUserId() > 0) {
				sql = sql + " and log_userid_fk = ? ";
			}
			if (info.getLogActionId() > 0) {
				sql = sql + " and log_action_id = ? ";
			}
			if (info.getLogChannelId() > 0) {
				sql = sql + " and log_userid_fk = ? ";
			}
			if (StringUtils.isNotBlank(startDate)) {
				sql = sql + " and log_time >= ? ";
			}
			if (StringUtils.isNotBlank(endDate)) {
				sql = sql + " and log_time <= ? ";
			}
			sql = sql + " order by log_time desc limit ? , ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);

			int i = 0;
			if (info.getUserId() > 0) {
				i++;
				pstmt.setInt(i, info.getUserId());
			}
			if (info.getLogActionId() > 0) {
				i++;
				pstmt.setInt(i, info.getLogActionId());
			}
			if (info.getLogChannelId() > 0) {
				i++;
				pstmt.setInt(i, info.getLogChannelId());
			}
			if (StringUtils.isNotBlank(startDate)) {
				i++;
				pstmt.setString(i, startDate + " 00:00:00");
			}
			if (StringUtils.isNotBlank(endDate)) {
				i++;
				pstmt.setString(i, endDate + " 23:59:59");
			}
			i++;
			pstmt.setInt(i, start);
			i++;
			pstmt.setInt(i, num);
			log.info(pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			LogInfo log = null;
			while (rs.next()) {
				log = new LogInfo();
				log.setId(rs.getInt("id"));

				log.setUserId(rs.getInt("log_userid_fk"));
				log.setUserInfo(new UserInfoDao().getUser(log.getUserId()));

				log.setLogActionId(rs.getInt("log_action_id"));
				log.setLogActionName((String) LogConstants.LOGACTIONMAP
						.get(Integer.valueOf(log.getLogActionId())));

				log.setLogChannelId(rs.getInt("log_channelid_fk"));
				log.setChannelInfo(new ChannelInfoDao()
						.getChannelByChannelId(log.getLogChannelId()));

				log.setLogDesc(rs.getString("log_desc"));
				log.setLogTime(rs.getString("log_time"));

				list.add(log);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public int getCount(LogInfo info, String startDate, String endDate) {
		int count = 0;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as count from tbl_log_i where 1=1 ";

			if (info.getUserId() > 0) {
				sql = sql + " and log_userid_fk = ? ";
			}
			if (info.getLogActionId() > 0) {
				sql = sql + " and log_action_id = ? ";
			}
			if (info.getLogChannelId() > 0) {
				sql = sql + " and log_userid_fk = ? ";
			}
			if (StringUtils.isNotBlank(startDate)) {
				sql = sql + " and log_time >= ? ";
			}
			if (StringUtils.isNotBlank(endDate)) {
				sql = sql + " and log_time <= ? ";
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);

			int i = 0;
			if (info.getUserId() > 0) {
				i++;
				pstmt.setInt(i, info.getUserId());
			}
			if (info.getLogActionId() > 0) {
				i++;
				pstmt.setInt(i, info.getLogActionId());
			}
			if (info.getLogChannelId() > 0) {
				i++;
				pstmt.setInt(i, info.getLogChannelId());
			}
			if (StringUtils.isNotBlank(startDate)) {
				i++;
				pstmt.setString(i, startDate + " 00:00:00");
			}
			if (StringUtils.isNotBlank(endDate)) {
				i++;
				pstmt.setString(i, endDate + " 23:59:59");
			}

			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt("count");
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public void log(int userId, int logActionId, int logChannelId, String desc) {
		LogInfo info = new LogInfo();
		info.setUserId(userId);
		info.setLogActionId(logActionId);
		info.setLogChannelId(logChannelId);
		info.setLogDesc(desc);
		info.setLogTime(DateFormatUtils.format(new Date(),
				"yyyy-MM-dd HH:mm:ss"));
		new LogInfoDao().insert(info);
	}

	public LogOpr getOprCount(int userId, String startDate, String endDate) {
		LogOpr opr = new LogOpr();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "SELECT count(id) as total,log_action_id FROM tbl_log_i  where  log_userid_fk = ? ";

			if (StringUtils.isNotBlank(startDate)) {
				sql = sql + " and log_time >= ? ";
			}
			if (StringUtils.isNotBlank(endDate)) {
				sql = sql + " and log_time <= ? ";
			}
			sql = sql + "  group by log_action_id ;";

			PreparedStatement pstmt = conn.prepareStatement(sql);

			int i = 0;
			i++;
			pstmt.setInt(i, userId);
			if (StringUtils.isNotBlank(startDate)) {
				i++;
				pstmt.setString(i, startDate + " 00:00:00");
			}
			if (StringUtils.isNotBlank(endDate)) {
				i++;
				pstmt.setString(i, endDate + " 23:59:59");
			}

			ResultSet rs = pstmt.executeQuery();
			int count = 0;
			int actionId = 0;
			while (rs.next()) {
				count = rs.getInt("total");
				actionId = rs.getInt("log_action_id");
				if (actionId == 1) {
					opr.setInsert(count);
				}
				if (actionId == 2) {
					opr.setDelete(count);
				}
				if (actionId == 3) {
					opr.setModify(count);
				}
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return opr;
	}

	public List<LogReportDTO> getReportList(String startDate, String endDate,
			int start, int num) {
		List<LogReportDTO> list = new ArrayList<LogReportDTO>();
		LogReportDTO dto = null;
		List<UserInfo> userInfoList = new UserInfoDao().getUserListByRoleId(-1,
				start, num);
		for (UserInfo userInfo : userInfoList) {
			dto = new LogReportDTO();
			dto.setUserInfo(userInfo);
			dto.setLogOpr(getOprCount(userInfo.getId(), startDate, endDate));
			list.add(dto);
		}
		return list;
	}
}